﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Persistencia.Interfaces;

namespace Persistencia
{
    public class PersistibleDB : IPersistibleDB
    {
        private string hashDatosOriginal;
        private string id;
        private TransaccionBase tran;
        private Factoria factoria;
        private IPersistibleModelo objeto;
        private bool eliminado;
        private bool suplantado;
        private string guid = Guid.NewGuid().ToString();
        internal bool Suplantado
        {
            get
            {
                return suplantado;

            }
            set
            {
                suplantado = value;
            }
        }


        public void Insert()
        {
            
            SqlCommand comm = tran.Conexion.CreateCommand();
           
            comm.Transaction = tran.SQLTran;
            HashTableNCSensitive hts = new HashTableNCSensitive();
            hts=this.factoria.ParametrosClave();
            this.objeto.CargarParametrosClave(hts);

            HashTableNCSensitive htsNoClave = this.factoria.ParametrosNoClave();
            foreach (string clave in htsNoClave.Keys)
                hts.Add(clave, htsNoClave[clave]);
                      

            this.objeto.CargarParametrosNoClave(hts);
            string comando="INSERT INTO " + this.factoria.NombreTabla+" (";
            string separador=" , ";
            int i=1;
            List<string> values = new List<string>();
            foreach(string s in hts.Keys)
            {
               values.Add("@" + s);
               i++;
               comando+=s+separador;
               if(i==hts.Count)
                  separador=" )";
            }

            comando+=" VALUES (";
            separador=" , ";
            i=1;

            

            foreach (string s in values)
            {
                if (hts[s.Remove(0, 1)] != null)
                {
                        comm.Parameters.Add(new SqlParameter(s, hts[s.Remove(0, 1)]));

                }
                else
                    comm.Parameters.Add(new SqlParameter(s, System.DBNull.Value));
               
                i++;
                comando += s + separador;
                if (i == hts.Count)
                    separador = " )";
            }

            comm.CommandText = comando;

            this.tran.EjecutarComandoSinRespuesta(comm);
            if (this.objeto is IIdentityEntity && this.factoria.UltimoId != null)
            {
                SqlCommand commUltimoId = tran.Conexion.CreateCommand();
                commUltimoId.CommandText = "UPDATE NUESTRA_RUBIA_ES_MEJOR.UltimoId SET UltimoId=" + this.factoria.UltimoId.Valor +  " WHERE NOMBRE_TABLA='" + this.factoria.NombreTabla.Trim() + "'";
                commUltimoId.Transaction = tran.SQLTran;
                this.tran.EjecutarComandoSinRespuesta(commUltimoId);
                ((IIdentityEntity)this.objeto).Id = this.factoria.UltimoId.Valor;
            }


        }

        public void Update()
        {
            SqlCommand comm = tran.Conexion.CreateCommand();
            comm.Transaction = tran.SQLTran;

            HashTableNCSensitive hts = new HashTableNCSensitive();
            HashTableNCSensitive htsWhere= new HashTableNCSensitive();
            this.objeto.CargarParametrosClave(htsWhere);
            this.objeto.CargarParametrosNoClave(hts);
            string comando = "UPDATE " + this.factoria.NombreTabla + " SET ";
            string separador = " , ";
            int i = 1;

            System.Collections.IDictionaryEnumerator enumerator =  hts.GetEnumerator();
            while(enumerator.MoveNext())
            {
                i++;
                comando += enumerator.Key.ToString()+"=@"+enumerator.Key.ToString() + separador;
                if (i == hts.Count)
                    separador = "  ";

                if (enumerator.Value != null)
                {
                        comm.Parameters.Add(new SqlParameter("@" + enumerator.Key, enumerator.Value));
                }
                else
                    comm.Parameters.Add(new SqlParameter("@" + enumerator.Key, System.DBNull.Value));
            }

            i = 0;
            comando += "WHERE ";
            enumerator =  htsWhere.GetEnumerator();
            separador = " AND ";
            while(enumerator.MoveNext())
            {
                i++;
                if (i == htsWhere.Count)
                    separador = "";
                comando += enumerator.Key.ToString()+"=@"+enumerator.Key.ToString() + separador;
                
                comm.Parameters.Add(new SqlParameter("@" + enumerator.Key, enumerator.Value));
                
            }
            
            comm.CommandText = comando;

            this.tran.EjecutarComandoSinRespuesta(comm);
        }

        public void Delete()
        {
            SqlCommand comm = tran.Conexion.CreateCommand();
            comm.Transaction = tran.SQLTran;

            HashTableNCSensitive hts = this.factoria.ParametrosClave();
            this.objeto.CargarParametrosClave(hts);
            string comando = "DELETE FROM " + this.factoria.NombreTabla + " WHERE ";
            string separador = " AND ";
            int i = 0;

            System.Collections.IDictionaryEnumerator enumerator = hts.GetEnumerator();
            while (enumerator.MoveNext())
            {
                i++;
                if (i == hts.Count)
                    separador = "";
                comando += enumerator.Key.ToString() + "=" + enumerator.Value + separador;
                
            }

            comm.CommandText = comando;

            this.tran.EjecutarComandoSinRespuesta(comm);
        }


        public string Id
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }

        public TransaccionBase Transaccion
        {
            get
            {
                return tran;
            }
            set
            {
                tran = value;
            }
        }

        public Factoria Factoria
        {
            get
            {
                return factoria;
            }
            set
            {
                factoria = value;
            }
        }

        public string HashDatosOriginal
        {
            get
            {
                return hashDatosOriginal;
            }
            set
            {
                hashDatosOriginal = value;
            }
        }

        public IPersistibleModelo Objeto
        {
            get
            {
                return objeto;
            }
            set
            {
                objeto = value;
            }
        }


        public bool Eliminado
        {
            get
            {
                return eliminado;
            }
            set
            {
                eliminado = value;
            }
        }

    }
}
